package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.bo.Person;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Person Data Access Object
 * @author Jason Whiting
 */
public class PersonDAO
{

    /////////////////////////////////////////////
    ///   Singleton code
    private static PersonDAO instance = null;

    /** Creates a new instance of PersonDAO */
    private PersonDAO()
    {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized PersonDAO getInstance()
    {
        if (instance == null)
        {
            instance = new PersonDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new person in the database
     * set alreadyInDB to false
     * put person in cache
     */
    public Person create(String id) throws DataException
    {
        Person person = new Person(id);
        person.setObjectAlreadyInDB(false);
        Cache.getInstance().put(person.getId(), person);
        return person;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing person from the database
     * 1) Checks cache
     * 2) Gets a connection
     * 3) Calls read with connection argument
     */
    public Person read(String id) throws DataException
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (Person) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try
        {
            return read(id, conn);
        } catch (Exception e)
        {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally
        {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing person from the database
     * @return person
     */
    synchronized Person read(String id, Connection conn) throws Exception
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (Person) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM person where id=?");

        try
        {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next())
            {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("Person with id '" + id + "' not found.");
        } finally
        {
            stmt.close();
        }
    }//read

    /** Internal method to create a person object from a record  */
    synchronized Person readRecord(ResultSet rs, Connection conn) throws Exception
    {
        if (Cache.getInstance().containsKey(rs.getString("id")))
        {
            return (Person) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new person object
        Person person = new Person(rs.getString("id"));

        // Person is already in DB
        person.setObjectAlreadyInDB(true);

        // Put person in cache
        Cache.getInstance().put(person.getId(), person);

        // Set Person Attributes
        person.setFirstName(rs.getString("first_name"));
        person.setLastName(rs.getString("last_name"));
        person.setAddress(rs.getString("address"));
        person.setPhone(rs.getString("phone"));

        // Person is dirty and needs to be saved to DB
        person.setDirty(false);

        return person;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing person in the database */
    public void save(Person person) throws DataException
    {
        // get a connection from the pool, save all of the sales first using the other person method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try
        {
            save(person, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e)
        {
            try
            {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2)
            {
                throw new DataException("Could not roll back the person database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the person object information.", e);
        } finally
        {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a person in the database */
    void save(Person person, Connection conn) throws Exception
    {
        // put the person object to cache
        Cache.getInstance().put(person.getId(), person);

        // decide whether to update or insert into the database
        if (person.isObjectAlreadyInDB() && person.isDirty())
        {
            update(person, conn);
        } else if (person.isDirty())
        {
            insert(person, conn);
        }//if
    }

    /** External method to update or create a person in the database without committing connection */
    public void saveNoCommit(Person person, Connection conn) throws Exception
    {
        Boolean alreadyInDB = person.isObjectAlreadyInDB();
        // decide whether to update or insert into the database
        if (alreadyInDB)
        {
            update(person, conn);
        } else
        {
            insert(person, conn);
        }
    }

    /** Saves an existing person to the database */
    private void update(Person person, Connection conn) throws Exception
    {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE person SET first_name=?, last_name=?, address=?, phone=?  WHERE id=?");
        try
        {
            // Get Person Attributes and add them to the statement
            stmt.setString(1, person.getFirstName());
            stmt.setString(2, person.getLastName());
            stmt.setString(3, person.getAddress());
            stmt.setString(4, person.getPhone());
            stmt.setString(5, person.getId());
            stmt.execute();

            // Person is clean!
            person.setDirty(false);
        } finally
        {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new person into the database */
    private void insert(Person person, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO person (id, first_name, last_name, address, phone) VALUES (?, ?, ?, ?, ?)");
        try
        {
            // Set Person Attributes
            stmt.setString(1, person.getId());
            stmt.setString(2, person.getFirstName());
            stmt.setString(3, person.getLastName());
            stmt.setString(4, person.getAddress());
            stmt.setString(5, person.getPhone());
            stmt.execute();

            person.setObjectAlreadyInDB(true); // now in DB
            person.setDirty(false); // object is clean
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Person person) throws DataException
    {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Retrieves all persons in a list of arrays from the database */
    public List<String[]> getAll() throws DataException
    {
        //this method does not use the standard search method because we don't want to get EVERY person object back from here, there could be 10's of
        //thousands in the future. instead, we want their full name and phone number so we can populate our search list. so our query should be designed
        //as such. this method will return a List of type String array.
        return null;
    }

    /** Internal method to search by certain fields */
    private List<Person> search(PreparedStatement stmt, Connection conn) throws Exception
    {
        List<Person> persons = new LinkedList<Person>();
        try
        {
            ResultSet rs = stmt.executeQuery();
            while (rs.next())
            {
                persons.add(readRecord(rs, conn));
            }

        } finally
        {
            stmt.close();
        }

        return persons;
    }

    public Person getByName(String fName, String lName, String pNumber) throws DataException
    {
        //establish connection and prepared statement run it through the search method, return a list of persons matching the Name and phone number
        return null;
    }

    ////////////////////////////////////////////////
    ///   Set methods
    /** sets all the employee values necessary */
    public Person setPersonValues(Person person, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM person where id=?");
        try
        {
            // Prevent SQL injection
            stmt.setString(1, person.getId());

            // Execute query
            ResultSet rs = stmt.executeQuery();

            // If we found a record, determine what to do.
            if (rs.next())
            {
                // Set each attribute to object
                person.setFirstName(rs.getString("first_name"));
                person.setLastName(rs.getString("last_name"));
                person.setAddress(rs.getString("address"));
                person.setPhone(rs.getString("phone"));

                return person;
            }

            // Throw a data exception because we couldn't find the username with the given password
            throw new DataException("Person with id '" + person.getId() + "' not found.");
        } finally
        {
            // Ensure we close the connection no matter what.
            stmt.close();
        }
    }
}
